﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace SBLib.Common.CsvKS
{
    public class SQLWriter : CsvBase
    {
        string filename = string.Empty;
        string delimiter = string.Empty;
        string connectionstring = string.Empty;
        string sqlstatement = string.Empty;

        public SQLWriter(string FileName, string Delimiter, string ConnectionString, string SQLStatement)
            : base(FileName, Delimiter)
        {
            filename = FileName;
            delimiter = Delimiter;
            connectionstring = ConnectionString;
            sqlstatement = SQLStatement;

        }

        private DataTable GetDataTable()
        {
            SqlDataAdapter dataAdapter = null;
            SqlCommandBuilder commandBuilder = null;
            try
            {
                dataAdapter = new SqlDataAdapter(sqlstatement, connectionstring);
                //commandBuilder = new SqlCommandBuilder(dataAdapter);

                // Populate a new data table and bind it to the BindingSource.
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(table);
                return table;
            }
            catch (Exception)
            {

            }
            finally
            {
                if (commandBuilder != null)
                {
                    commandBuilder.Dispose();
                }
                if (dataAdapter != null)
                {
                    dataAdapter.Dispose();
                }


            }

            return null;
        }


        protected override void ProcessObjectForWriting()
        {
            // Header
            DataTable dt = GetDataTable();
            if (dt == null)
                return;

            string[] colLbls = new string[dt.Columns.Count];

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                colLbls[i] = dt.Columns[i].ColumnName;
                colLbls[i] = GetWriteableValueForCsv(colLbls[i]);
            }

            // Write labels
            _swFile.WriteLine(string.Join(delimiter, colLbls));

            // Rows of Data
            foreach (DataRow rowData in dt.Rows)
            {
                string[] colData = new string[dt.Columns.Count];

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    object obj = rowData[i];
                    colData[i] = GetWriteableValueForCsv(obj);
                }

                // Write data in row
                _swFile.WriteLine(string.Join(delimiter, colData));
            }
        }



        protected override void ProcessObjectForWriting<T>(List<T> CustomObjectCollection)
        {

        }

    }
}
